package teacher.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Paper;
import teacher.bean.Patent;
import teacher.bean.Project;
import teacher.bean.Teacher;
import teacher.bean.Treatise;
import teacher.page.Page;
import database.dao.MysqlCrud;
import tools.StringDate;

public class project {
	
	/**
	 * @category 通过论著ID查询一项项目
	 * @return paperOne List<Paper> 返回查询到的论文列表
	 */
	public Project selectProjectById(int a) {
		Project p = new Project();
		String sql= "select * from project where proId=?";
		int i=0;	
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(a)};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		if(1==list.size()){
			Map map=list.get(0);
			p=new MapToBean().MapToProject(map);
		}
		return p;
	}
	
	/**
	 * @category 用于项目信息的修改
	 * @param treatise 修改的内容
	 * @param a 定位修改的位置
	 * @return 修改成功，返回true，否则返回flase
	 */
	public boolean updateProject(Project project,int a) {
		String sql= "update project set proId=?,name=?,level=?,source=?,funds=?,arriveFunds=?,leader=?,others=?,beginDate=?,state=?," +
				"lowDepartment=?,class=?,number=?,finishDate=?,upload=?,teaId=?,kind=?,progress=? where proId="+a+"";
		String[] parameter = {
				String.valueOf(project.getProId()),
				String.valueOf(project.getName()),
				String.valueOf(project.getLevel()),
				String.valueOf(project.getSource()),
				String.valueOf(project.getFunds()),
				String.valueOf(project.getArriveFunds()),
				String.valueOf(project.getLeader()),
				String.valueOf(project.getOthers()),
				String.valueOf(new StringDate().DateToString(project.getBeginDate())),
				String.valueOf(project.getState()),
				String.valueOf(project.getLowDepartment()),
				String.valueOf(project.getClass_()),
				String.valueOf(project.getNumber()),
				String.valueOf(new StringDate().DateToString(project.getFinishDate())),
				String.valueOf(project.getUpload()),
				String.valueOf(project.getTeaId()),
				String.valueOf(project.getKind()),
				String.valueOf(project.getProgress())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		boolean flag= mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 统计一个老师一定时间项目的数量
	 * @return sum int 论著的数量
	 */
		public int getCountProjectTeaTime(Teacher tea,Date date1,Date date2)
		{
			int sum=0;
			String[] parameter={String.valueOf(tea.getTeaId())};
			String sql="select count(*) from project where finishDate>='"+date1+"' and finishDate<='"+date2+"' and teaId=?";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		
		/**
		 * @category 统计一个老师按照模糊查询项目的数量
		 * @return sum int 论著的数量
		 */
			public int getCountProjectTeaVague(String vague,Teacher t)
			{
				int sum=0;
				String[] parameter={String.valueOf(t.getTeaId())};
				String sql="select count(*) from project where name like '%"+vague+"%' and teaId=?";
				MysqlCrud mysqlcurd=new MysqlCrud();
				List<Map> lists=mysqlcurd.SelectData(sql, parameter);
				Map map=lists.get(0);
				sum=Integer.parseInt(map.get(0).toString());
				return sum;
			}
	
	/**
	 * @category 通过老师查询项目分页
	 * @param tea Teacher 老师信息
	 * @return paperOne List<Paper> 返回查询到的论文列表
	 */
	public List<Project> selectProjectByTeacherPage(Teacher t1,Page page) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where acheve.teaId=? limit ?,?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		Integer[] parameter={t1.getTeaId(),page.getBeginIndex(),page.getEveryPage()};
		List<Map> list = mysqlcurd.SelectDataInt(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过时间段查询老师项目分页
	 * @return paperOne List<Treatise> 返回查询到的专利列表
	 */
	public List<Project> selectTimeProjectTeaPage(Teacher t,Date date1,Date date2,Page page) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where acheve.finishDate>='"+date1+"' and acheve.finishDate<='"+date2+"' and acheve.teaId=? limit ?,?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		Integer[] parameter={t.getTeaId(),page.getBeginIndex(),page.getEveryPage()};
		List<Map> list = mysqlcurd.SelectDataInt(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过字段模糊查询老师项目分页
	 * @param vague 模糊查询需要字段
	 * @return treatiseOne List<Treatise> 返回查询到的论著列表
	 */
	public List<Project> selectVagueProjectTea(String vague,Teacher t1,Page page) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where acheve.name like '%"+vague+"%' and acheve.teaId=? limit ?,?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		Integer[] parameter={ t1.getTeaId(),page.getBeginIndex(),page.getEveryPage()};
		List<Map> list = mysqlcurd.SelectDataInt(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过时间段查询老师项目
	 * @return paperOne List<Project> 返回查询到的专利列表
	 */
	public List<Project> selectTimeProjectTea(Teacher t,Date date1,Date date2) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where acheve.finishDate>='"+date1+"' and acheve.finishDate<='"+date2+"' and acheve.teaId=?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(t.getTeaId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过老师查询国家级项目
	 * @param tea Teacher 老师信息
	 * @return projectOne List<Paper> 返回查询到的论文列表
	 */
	public List<Project> selectCountryLevel(Teacher tea) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve,teacher as t where acheve.teaId = t.teaId and acheve.level=0 and t.user = ?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(tea.getUser())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 查询所有项目
	 * @return projectOne List<Project> 返回查询到的论文列表
	 */
	public List<Project> selectAllProject() {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where (acheve.state=4 or acheve.state=5)";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过老师查询项目
	 * @param tea Teacher 老师信息
	 * @return projectOne List<Paper> 返回查询到的论文列表
	 */
	public List<Project> selectProjectByTeacher(Teacher tea) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve,teacher as t where acheve.teaId = t.teaId and t.user = ?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(tea.getUser())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过老师查询国家级项目
	 * @param tea Teacher 老师信息
	 * @return projectOne List<Paper> 返回查询到的论文列表
	 */
	public List<Project> selectProvinceLevel(Teacher tea) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve,teacher as t where acheve.teaId = t.teaId and acheve.level=1 and t.user = ?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(tea.getUser())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}
	
	/**
	 * @category 通过老师查询国家级项目
	 * @param tea Teacher 老师信息
	 * @return projectOne List<Paper> 返回查询到的论文列表
	 */
	public List<Project> selectAbroadLevel(Teacher tea) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve,teacher as t where acheve.teaId = t.teaId and acheve.level=2 and t.user = ?";
		int i=0;
		List<Project> projectOne=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(tea.getUser())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projectOne.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projectOne;
	}

	
	/**
	 * @category 统计项目的数量
	 * @return sum int 项目的数量
	 */
		public int getCountProject()
		{
			int sum=0;
			String sql="select count(*) from project where state>3";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
	
		/**
		 * @category 通过字段模糊查询项目
		 * @param vague 模糊查询需要字段
		 * @return projectOne List<Project> 返回查询到的项目列表
		 */
		public List<Project> selectVagueProject(String vague) {
			String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where (acheve.state=4 or acheve.state=5) and acheve.name like '%"+vague+"%'";
			int i=0;
			List<Project> projectOne=new ArrayList<Project>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, null);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				projectOne.add(new MapToBean().MapToProject(map));
				i++;
			}
			return projectOne;
		}
		
		/**
		 * @category 通过字段模糊查询老师项目
		 * @param vague 模糊查询需要字段
		 * @return projectOne List<Project> 返回查询到的项目列表
		 */
		public List<Project> selectVagueProjectTea(String vague,Teacher t) {
			String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where acheve.name like '%"+vague+"%' and acheve.teaId=?";
			int i=0;
			List<Project> projectOne=new ArrayList<Project>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[] parameter={String.valueOf(t.getTeaId())};
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				projectOne.add(new MapToBean().MapToProject(map));
				i++;
			}
			return projectOne;
		}
		
		/**
		 * @category 通过时间段查询项目
		 * @return projectOne List<Project> 返回查询到的项目列表
		 */
		public List<Project> selectTimeProject(Date date1,Date date2) {
			String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as acheve where (acheve.state=4 or acheve.state=5) and acheve.finishDate>='"+date1+"' and acheve.finishDate<='"+date2+"'";
			int i=0;
			List<Project> projectOne=new ArrayList<Project>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, null);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				projectOne.add(new MapToBean().MapToProject(map));
				i++;
			}
			return projectOne;
		}
		
		/**
		 * @category 插入一条项目数据
		 * @param project 所要插入的项目数据
		 * @return 插入成功返回true，否则返回false
		 */
		public boolean insertProject(Project project){
			boolean flag = false;
			String sql="insert into project(name,level,source,funds,arriveFunds,leader,others,beginDate,state,lowDepartment,class,number,finishDate,upload,teaId,kind,progress)"+" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			String[] parameter={
					String.valueOf(project.getName()),
					String.valueOf(project.getLevel()),
					String.valueOf(project.getSource()),
					String.valueOf(project.getFunds()),
					String.valueOf(project.getArriveFunds()),
					String.valueOf(project.getLeader()),
					String.valueOf(project.getOthers()),
					String.valueOf(new StringDate().DateToString(project.getBeginDate())),
					String.valueOf(project.getState()),
					String.valueOf(project.getLowDepartment()),
					String.valueOf(project.getClass_()),
					String.valueOf(project.getNumber()),
					String.valueOf(new StringDate().DateToString(project.getFinishDate())),
					String.valueOf(project.getUpload()),
					String.valueOf(project.getTeaId()),
					String.valueOf(project.getKind()),
					String.valueOf(project.getProgress())
			};
			MysqlCrud mysqlcurd=new MysqlCrud();
			flag = mysqlcurd.InsertAndUpdateData(sql, parameter);
			return flag;
		}
		
		/**
		 * @category 统计一个老师项目的数量
		 * @return sum int 项目的数量
		 */
			public int getCountProjectTea(Teacher tea)
			{
				int sum=0;
				String[] parameter={String.valueOf(tea.getTeaId())};
				String sql="select count(*) from project where teaId=?";
				MysqlCrud mysqlcurd=new MysqlCrud();
				List<Map> lists=mysqlcurd.SelectData(sql, parameter);
				Map map=lists.get(0);
				sum=Integer.parseInt(map.get(0).toString());
				return sum;
			}

			/**
			 * @category 用于删除项目的信息
			 * @return 删除成功返回true，否则为false
			 */
			public boolean DeleteProject(Project project){
				String sql = "delete from project where proId=?";
				String[]  parameter={String.valueOf(project.getProId())};
				 MysqlCrud mysqlcurd=new MysqlCrud();
				 boolean flag= mysqlcurd.InsertAndUpdateData(sql, parameter);
				 return flag;
			}

}
